package com.gitee.neuray.security.generator.utils;

import cn.hutool.core.exceptions.ExceptionUtil;
import cn.hutool.db.DbUtil;
import cn.hutool.db.Session;
import com.alibaba.druid.pool.DruidDataSource;
import com.gitee.neuray.security.common.exception.BaseException;
import com.gitee.neuray.security.generator.databasemd.entity.TDatabaseInfoEntity;
import com.google.common.collect.Lists;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.cloud.context.config.annotation.RefreshScope;
import org.springframework.stereotype.Component;
import org.springframework.util.StringUtils;

import javax.sql.DataSource;
import java.sql.*;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author: zhhongyu
 * @description: hutool数据源配置工具类
 * @since: Create in 13:32 2020/2/14
 */
@Slf4j
@Component
@RefreshScope
public class DataSourceUtils {
    @Value("${code.db.maxwait}")
    private long dbMaxWait;

    /**
     * 创建数据库
     *
     * @param tDatabaseInfoEntity
     * @return
     */
    public DataSource createdb(TDatabaseInfoEntity tDatabaseInfoEntity) {
        try {
            //具体的配置参数请参阅Druid官方文档
            DruidDataSource ds2 = new DruidDataSource();
            ds2.setUrl(tDatabaseInfoEntity.getDbUrl());
            ds2.setUsername(tDatabaseInfoEntity.getUserName());
            ds2.setPassword(tDatabaseInfoEntity.getPassword());
            //连接等待时间为默认为4秒钟·
            if (dbMaxWait == 0) {
                dbMaxWait = 4000;
            }
            ds2.setMaxWait(dbMaxWait);
            return ds2;
        } catch (Exception e) {
            log.error(ExceptionUtil.getMessage(e));
            return null;
        }
    }

    /**
     * 执行数据库语句判定是否能连通数据库
     *
     * @return
     */
    public boolean excutedb(DataSource ds) throws Exception {
        try {
            //确定使用的ds
            Session session = DbUtil.newSession(ds);
            List<Integer> query = session.query("SELECT 1+1 from dual", Integer.class, null);
            return true;
        } catch (Exception e) {
            log.error(ExceptionUtil.getMessage(e));
            throw e;
        } finally {
            //结束后关闭数据库连接
            DbUtil.close(ds);
        }
    }

    /**
     * 指定数据源创建视图(此方法不关闭连接)
     *
     * @param ds       数据源
     * @param sql      单表或多表查询语句（sql必须要以select或者SELECT开头）
     * @param viewName 视图名称
     * @return 视图名称
     * @throws Exception
     */
    public String createView(DataSource ds, String sql, String viewName) throws Exception {
        checkSqlEmpty(sql);
        sql = sql.toUpperCase();
        if (!sql.startsWith("SELECT")) {
            throw new BaseException("待创建的sql必须要以select或SELECT 开头...");
        }
        StringBuilder creatViewSQL = new StringBuilder();
        creatViewSQL.append(" CREATE OR REPLACE VIEW      ");
        creatViewSQL.append(viewName);
        creatViewSQL.append("    AS    ");
        creatViewSQL.append(sql);
        excute(ds, creatViewSQL.toString());
        return viewName;
    }

    /**
     * 删除视图
     *
     * @param ds       数据源
     * @param viewName 视图名称
     * @throws Exception
     */
    public DataSourceUtils dropView(DataSource ds, String viewName) throws Exception {
        StringBuilder sql = new StringBuilder();
        sql.append("drop view ");
        sql.append(viewName);
        excute(ds, sql.toString());
        return this;
    }

    /**
     * 指定数据源执行sql
     *
     * @param ds  数据源
     * @param sql 正确的sql语句
     * @throws Exception
     */
    public void excute(DataSource ds, String sql) throws Exception {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = ds.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            //执行
            preparedStatement.execute();
        } catch (Exception e) {
            log.error(ExceptionUtil.getMessage(e));
            throw e;
        } finally {
            if (preparedStatement != null) {
                preparedStatement.close();
            }
            if (connection != null) {
                connection.close();
            }
        }
    }

    /**
     * 执行查询
     *
     * @param ds
     * @param sql
     * @return
     * @throws Exception
     */
    public ResultSet excuteQuery(DataSource ds, String sql) throws Exception {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = ds.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            //执行
            ResultSet resultSet = preparedStatement.executeQuery();
            return resultSet;
        } catch (Exception e) {
            log.error(ExceptionUtil.getMessage(e));
            throw e;
        }
    }

    /**
     * 指定枚举类遍历查询结果
     * @param resultSet
     * @param clazz
     * @return
     * @throws Exception
     */
    public List<Map> forEachResultSet(ResultSet resultSet, Class clazz) throws Exception {
        ResultSetMetaData rsmd = resultSet.getMetaData();
        int count = rsmd.getColumnCount();
        List<Map> result = Lists.newArrayList();
        Map<String, Object> allEnum = CodeEnumUtils.getAllEnum(clazz);
        while (resultSet.next()) {
            Map<String, String> columnMap = new HashMap<>();
            for (int i = 1; i <= count; i++) {
                columnMap.put((String) allEnum.get(i+""), resultSet.getString(i));
            }
            result.add(columnMap);
        }
        return result;
    }

    public Map<String, String> forEachResultSetMap(ResultSet resultSet, Class clazz) throws Exception {
        ResultSetMetaData rsmd = resultSet.getMetaData();
        int count = rsmd.getColumnCount();
        Map<String, Object> allEnum = CodeEnumUtils.getAllEnum(clazz);
        Map<String, String> columnMap = new HashMap<>();
        while (resultSet.next()) {
            for (int i = 1; i <= count; i++) {
                columnMap.put((String) allEnum.get(i+""), resultSet.getString(i));
            }
        }
        return columnMap;
    }

    /**
     * 关闭数据源，关闭预处理器，关闭连接
     *
     * @param connection        连接
     * @param preparedStatement 预处理器
     * @throws SQLException sql关闭异常
     */
    public void close(DataSource ds,
                      Connection connection,
                      PreparedStatement preparedStatement) throws SQLException {
        if (preparedStatement != null) {
            preparedStatement.close();
        }
        if (connection != null) {
            connection.close();
        }
        if (ds != null) {
            DbUtil.close(ds);
        }
    }


    /**
     * 校验sql是否为空
     *
     * @param sql
     */
    private void checkSqlEmpty(String sql) {
        if (StringUtils.isEmpty(sql)) {
            throw new BaseException("sql不能为空...");
        }
    }
}
